﻿using CommonModel.DB;
using CommonModel.Field;
using CommonModel.Model;
using Maikebing.Data.Taos;
using Microsoft.EntityFrameworkCore;
using NewLife.Log;
using PtlSave.Model;
using PtlSave.Util;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Text;
using System.Threading;
using System.Threading.Tasks;

namespace PtlSave
{
    /*
     * 数据库相关初始化
     */
    public class DataBaseInit
    {
        //涛思数据库连接信息
        public TaosConnectionStringBuilder builder = null;
        //mysql数据库连接信息
        public string connectionString = "";
        public string version = "";

        //每个配置文件一份SchemaDocument
        public Dictionary<int, SchemaDocument> cfgsAlone { get; set; } = null;
        public Dictionary<int, TableInfo> tableInfos { get; set; } = null;

        //所有数据表
        public Dictionary<string, Object> dataTables;

        private MySQLContext mySQLContext = null;
        //改为使用ADO.Net
        //private TaosDBContext taosDBContext = null;

        private DbConnection mysqlDbConnection = null;
        private TaosConnection taosDbConnection = null;

        public bool mysqlConn = false;
        public bool taosConn = false;

        public DataBaseInit(TaosConnectionStringBuilder builder, string connectionString, string version)
        {
            this.builder = builder;
            this.connectionString = connectionString;
            this.version = version;
            ExecInit();
        }

        public void ExecInit()
        {
            Init();
        }

        private void Init()
        {
            //mysql数据库初始化
            XTrace.WriteLine("MySQL初始化 。。。");
            DbContextOptions<MySQLContext> options = new DbContextOptions<MySQLContext>();
            mySQLContext = new MySQLContext(options, connectionString, version);
            try
            {
                mysqlDbConnection = mySQLContext.Database.GetDbConnection();
                mysqlConn = true;
                initSchemaMapFromDb();
            }
            catch (Exception e)
            {
                mysqlConn = false;
                cfgsAlone = new Dictionary<int, SchemaDocument>();
                tableInfos = new Dictionary<int, TableInfo>();
                XTrace.WriteLine(CommonUtil.GetFormatDatetime() + " " + e.Message);
                XTrace.WriteLine(CommonUtil.GetFormatDatetime() + " MySQL数据库连接失败!");
                return;
            }

            //涛思数据库初始化
            XTrace.WriteLine("Taos初始化 。。。");
            //Entity Framework Core 
            //taosDBContext = new TaosDBContext(new DbContextOptionsBuilder().UseTaos(builder.ConnectionString).Options);
            try
            {
                //ADO.Net
                taosDbConnection = new TaosConnection(builder.ConnectionString);
                if (taosDbConnection.State != System.Data.ConnectionState.Open)
                {
                    taosDbConnection.Open();
                }
                taosConn = true;
                CreateOrUpdDataTables();
            }
            catch (Exception e)
            {
                taosConn = false;
                XTrace.WriteLine(CommonUtil.GetFormatDatetime() + " " + e.Message);
                XTrace.WriteLine(CommonUtil.GetFormatDatetime() + " TAOS(涛思)数据库连接失败!");
            }
        }

        public bool ExecuteNonQuery(string sql)
        {
            bool rs = true;
            try
            {
                XTrace.WriteLine(sql);
                lock (taosDbConnection)
                {
                    if (taosDbConnection.State != System.Data.ConnectionState.Open)
                    {
                        taosDbConnection.Open();
                    }

                    using (TaosCommand commonTaosDBCommand = taosDbConnection.CreateCommand(sql))
                    {
                        commonTaosDBCommand.ExecuteNonQuery();
                    }
                    taosDbConnection.Close();
                }
            }
            catch
            {
                rs = false;
            }

            return rs;
        }

        /*
         * 更新动态字段最大最新数值
         */
        public void UpdCfg(int paramId, string col, string dynamicVal)
        {
            //赋值
            tableInfos[paramId].dynamic[col] = dynamicVal;

            //cfgsAlone配置修改
            cfgsAlone[paramId].setDynamicParaDictionary(tableInfos[paramId].dynamic);

            //tableInfos[paramId]重新初始化
            tableInfos[paramId].Init();

            //更新file_param_detail表
            try
            {
                lock (mysqlDbConnection)
                {
                    if (mysqlDbConnection.State != ConnectionState.Open)
                    { 
                        mysqlDbConnection.Open();
                    }
                    using (DbCommand commonMysqlDBCommand = mysqlDbConnection.CreateCommand())
                    {
                        commonMysqlDBCommand.CommandTimeout = 20;
                        commonMysqlDBCommand.CommandText = "update file_param_detail set loop_new = " + tableInfos[paramId].dynamic[col] + " where main_id = " + paramId + " and loop_val = '" + col + "'";
                        commonMysqlDBCommand.ExecuteNonQuery();
                    }
                    mysqlDbConnection.Close();
                }
            }
            catch { }

            //更新涛思数据库表
            try
            {
                lock (taosDbConnection)
                {
                    if (taosDbConnection.State != System.Data.ConnectionState.Open)
                    {
                        taosDbConnection.Open();
                    }
                    using (TaosCommand commonTaosDBCommand = taosDbConnection.CreateCommand())
                    {
                        commonTaosDBCommand.CommandTimeout = 20;
                        SetTableInfo(tableInfos[paramId], commonTaosDBCommand);
                    }
                    taosDbConnection.Close();
                }
            }
            catch { }
        }

        /*
         * 创建或更新数据表
         */
        public void CreateOrUpdDataTables()
        {
            XTrace.WriteLine(CommonUtil.GetFormatDatetime() + " 初始化表 。。。");

            lock (taosDbConnection)
            {
                if (taosDbConnection.State != System.Data.ConnectionState.Open)
                {
                    taosDbConnection.Open();
                }
                using (TaosCommand commonTaosDBCommand = taosDbConnection.CreateCommand())
                {
                    commonTaosDBCommand.CommandTimeout = 20;
                    //循环执行创建表语句
                    foreach (int key in tableInfos.Keys)
                    {
                        SetTableInfo(tableInfos[key], commonTaosDBCommand);
                    }
                }
                taosDbConnection.Close();
            }

            XTrace.WriteLine(CommonUtil.GetFormatDatetime() + " 初始化表完成");
        }

        /*
         * 更新数据库表
         * 此处创建的是超级表
         */
        private void SetTableInfo(TableInfo tableInfo, TaosCommand dbCommand, int times = 0, bool isCreateTB = true)
        {
            DbDataReader dataReader = null; 
            bool isTableExists = false;
            try
            {
                //创建表
                try
                {
                    if (isCreateTB)
                    { 
                        dbCommand.CommandText = tableInfo.GetCreateSTableSql();
                        dbCommand.ExecuteNonQuery();
                    }
                }
                catch
                {
                    if (!TryExecSqlToSucc(dbCommand, dbCommand.CommandText, 5))
                    {
                        XTrace.WriteLine(CommonUtil.GetFormatDatetime() + " 重试创建表失败!" + dbCommand.CommandText);
                    }
                }

                //获取子表
                try
                {
                    isTableExists = IsTableExists(dbCommand, tableInfo.tableName, true);

                    if (isTableExists)
                    {
                        tableInfo.tableNames.Clear();
                        dbCommand.CommandText = "SELECT DISTINCT TBNAME FROM " + tableInfo.tableName;
                        dataReader = dbCommand.ExecuteReader();
                        while (dataReader.Read())
                        {
                            tableInfo.tableNames.Add(dataReader.GetString(0).ToUpper(), 0);
                        }
                    }
                    else
                    {
                        if (times == 0)
                        {
                            SetTableInfo(tableInfo, dbCommand, ++times);
                        }
                    }
                }
                catch (Exception e)
                {
                    XTrace.WriteLine(e.Message + " 获取" + tableInfo.tableName + "子表失败，sql=" + dbCommand.CommandText);
                }

                List<string> colNotExists = new List<string>();
                try
                {
                    //获取表的结构信息
                    //通过表结构信息，判断哪些字段没有的，就新增
                    //查询结果结构 Field/Type/Length/Note
                    if (dataReader != null)
                    { 
                        dataReader.Close();
                    }
                    dbCommand.CommandText = "describe " + tableInfo.tableName;
                    dataReader = dbCommand.ExecuteReader();
                    while (dataReader.Read())
                    {
                        //包含再配置里面的就加入list，因为有时候修改参数后会产生表弃用字段
                        if (tableInfo.columnsStrFull.Contains(dataReader.GetValue(0).ToString().ToUpper()))
                        { 
                            colNotExists.Add(dataReader.GetValue(0).ToString().ToUpper());
                        }
                    }
                    dataReader.Close();
                }
                catch { }

                //不相等时，需要新增字段
                //修改顺序号等原因军可能造成不一致，故而使用ASCII排序再比对
                //分别把从表获取的字段和配置获取的字段按ASCII排序，如果两个字符串不一致，就进行字段校验新增
                string[] tbCols = colNotExists.ToArray();
                string[] cfgCols = tableInfo.columnsStrFull.Split(",");
                Array.Sort(tbCols, string.CompareOrdinal); //ASCII排序 string.CompareOrdinal参数说明是按ASCII排序
                Array.Sort(cfgCols, string.CompareOrdinal); //ASCII排序 string.CompareOrdinal参数说明是按ASCII排序

                //3个默认字段，2个tags
                if (colNotExists.Count != tableInfo.colNum ||
                    !String.Join("", tbCols).Equals(String.Join("", cfgCols)))
                {
                    foreach (int keyIn in tableInfo.columns.Keys)
                    {
                        string colName = tableInfo.columns[keyIn].columnNameTaos;
                        if (!tableInfo.columns[keyIn].isParent && colNotExists.FindAll(c => (c.ToUpper().Equals(colName.ToUpper()))).Count == 0)
                        {
                            try
                            {
                                dbCommand.CommandText = tableInfo.columns[keyIn].GetColAddSql(tableInfo.tableName);
                                dbCommand.ExecuteNonQuery();
                            }
                            catch (Exception e)
                            {
                                //XTrace.WriteLine(CommonUtil.GetFormatDatetime() + " 新增字段失败！" + e.Message + " " + dbCommand.CommandText);
                                //包含duplicated，说明是列明重复
                                if (e.Message.Contains("duplicated"))
                                {
                                    XTrace.WriteLine(CommonUtil.GetFormatDatetime() + " 新增字[" + tableInfo.columns[keyIn] + "]段失败，失败原因：字段重复。");
                                }
                                else if (!TryExecSqlToSucc(dbCommand, dbCommand.CommandText, 10))
                                {
                                    XTrace.WriteLine(CommonUtil.GetFormatDatetime() + " 重试新增字段失败!" + dbCommand.CommandText);
                                }
                            }
                        }
                    }
                }
            }
            catch { }
        }

        private bool IsTableExists(TaosCommand commonTaosDBCommand, string tableName, bool isSuper = false)
        {
            bool exists = false;
            try
            {
                lock (commonTaosDBCommand)
                {
                    commonTaosDBCommand.CommandText = "SHOW " + (isSuper? "STABLES" : "TABLES") + " Like '" + tableName + "'";
                    DbDataReader dataReader = commonTaosDBCommand.ExecuteReader();
                    while (dataReader.Read())
                    {
                        exists = true;
                        break;
                    }
                }
            }
            catch { }
            return exists;
        }

        private bool TryExecSqlToSucc(TaosCommand dbCommand, string sql, int times = 2)
        {
            bool rs = false;
            while (times > 0 && !rs)
            {
                try
                {
                    dbCommand.CommandText = sql;
                    dbCommand.ExecuteNonQuery();
                    rs = true;
                }
                catch(Exception e)
                {
                    //if (e.Message.Contains("Field already exists"))
                    if (e.Message.Contains("exists"))
                    {
                        rs = true;
                        break;
                    }
                }
                finally
                {
                    times--;
                }
            }
            return rs;
        }

        //读取xml配置
        //从数据库读取
        private Dictionary<int, SchemaDocument> initSchemaMapFromDb()
        {
            cfgsAlone = new Dictionary<int, SchemaDocument>();
            tableInfos = new Dictionary<int, TableInfo>();
            Dictionary<string, SchemaDocument> tmpDict = new Dictionary<string, SchemaDocument>();

            lock (mysqlDbConnection)
            {
                if (mysqlDbConnection.State != ConnectionState.Open)
                {
                    mysqlDbConnection.Open();
                }
                using (DbCommand dbCommand = mysqlDbConnection.CreateCommand())
                {
                    dbCommand.CommandTimeout = 20;

                    //从主表获取
                    //DbCommand dbCommand = mySQLContext.Database.GetDbConnection().CreateCommand();
                    //dbCommand.CommandText = "SELECT id, file_name, schema_name, msg_type, xml_type FROM file_param_main where id = 10 and use_flag = 1 ORDER BY id";
                    dbCommand.CommandText = "SELECT id, file_name, schema_name, msg_type, xml_type FROM file_param_main where use_flag = 1 ORDER BY id";
                    DbDataReader dataReader = dbCommand.ExecuteReader();
                    while (dataReader.Read())
                    {
                        SchemaDocument schDoc = new SchemaDocument();
                        schDoc.setName(dataReader.GetString(2));
                        schDoc.setFile_name(dataReader.GetString(1));
                        schDoc.setMsgType(dataReader.GetString(3));
                        schDoc.setXmlType(dataReader.GetString(4));
                        schDoc.setId(dataReader.GetValue(0).ToString());

                        if (!tmpDict.ContainsKey(schDoc.getFile_name()))
                        {
                            tmpDict.Add(schDoc.getFile_name(), schDoc);
                        }
                    }

                    string tableName = "file_param_detail";
                    //根据层级初始化数据 
                    int layerNum = 3;
                    dataReader.Close();
                    dbCommand.CommandText = "SELECT MAX(layer) layer FROM " + tableName;
                    dataReader = dbCommand.ExecuteReader();
                    if (dataReader.Read())
                    {
                        layerNum = int.Parse(dataReader.GetValue(0).ToString());
                    }

                    //循环获取配置元素数据
                    foreach (string key in tmpDict.Keys)
                    {
                        SchemaDocument schDoc = tmpDict[key];
                        Dictionary<int, Object> layerPre = new Dictionary<int, object>();
                        int maxLayer = 0;
                        for (int i = 0; i <= layerNum; i++)
                        {
                            layerPre.Add(i, new Dictionary<int, Object>());
                        }

                        /*****************从明细表获取************************/
                        dataReader.Close();
                        dbCommand.CommandText = "SELECT id,main_id,file_name,parent_id,child_num,idx,parent_idx,name,type,size,device_type,loop_val,code,isalarm,alarmvalue,alarmname,issave,get_time, memo,layer, loop_new" +
                            " FROM " + tableName + " a WHERE a.main_id = " + schDoc.getId() + " ORDER BY main_id, sort";
                        dataReader = dbCommand.ExecuteReader();

                        //动态字段
                        Dictionary<string, string> dynamic = new Dictionary<string, string>();
                        Dictionary<int, Object> schema = new Dictionary<int, Object>();
                        while (dataReader.Read())
                        {
                            //层级
                            int layer = int.Parse(dataReader.GetValue(19).ToString());
                            maxLayer = (layer > maxLayer ? layer : maxLayer);
                            int childNum = int.Parse(dataReader.GetValue(4).ToString());
                            if (!"".Equals(dataReader.GetString(11)) && !CommonUtil.IsInt(dataReader.GetString(11)) && !dynamic.ContainsKey(dataReader.GetString(11)))
                            {
                                //此处dynamic的value赋予默认值1
                                dynamic.Add(dataReader.GetString(11), "".Equals(dataReader.GetValue(20).ToString()) ? "1" : dataReader.GetValue(20).ToString());
                            }

                            if (layer == 0)
                            {
                                GetElement(dataReader, schema, childNum > 0 ? new SchemaDictionary() : new FieldSchema());
                                layerPre[layer] = schema[schema.Count - 1];
                            }
                            else
                            {
                                if ((((SchemaDictionary)layerPre[layer - 1])).getFieldDictionary() == null)
                                {
                                    ((SchemaDictionary)layerPre[layer - 1]).setFieldDictionary(new Dictionary<int, object>());
                                }
                                GetElement(dataReader, (((SchemaDictionary)layerPre[layer - 1])).getFieldDictionary(), childNum > 0 ? new SchemaDictionary() : new FieldSchema());
                                layerPre[layer] = (((SchemaDictionary)layerPre[layer - 1])).getFieldDictionary()[(((SchemaDictionary)layerPre[layer - 1])).getFieldDictionary().Count - 1];
                            }
                        }
                        dataReader.Close();
                        schDoc.setDynamicParaDictionary(dynamic);
                        schDoc.setFieldDictionary(schema);//将已读取XML文件后的XML字段规则放进schemaDocument
                        /*****************从明细表获取************************/

                        cfgsAlone.Add(int.Parse(schDoc.getId()), schDoc);
                        tableInfos.Add(int.Parse(schDoc.getId()), new TableInfo(schDoc) { layerNum = maxLayer });
                    }
                }
                mysqlDbConnection.Close();
            }
            return cfgsAlone;
        }

        //子元素获取
        private static void GetElement(DbDataReader dataReader, Dictionary<int, Object> schema, Object field)
        {
            ((dynamic)field).setName(dataReader.GetString(7));
            ((dynamic)field).setIndex(int.Parse(dataReader.GetValue(5).ToString()));
            ((dynamic)field).setId(dataReader.GetValue(0).ToString());
            ((dynamic)field).setType(DataTypeUtils.getType(dataReader.GetString(8)));
            ((dynamic)field).setMemo(dataReader.GetString(18));
            ((dynamic)field).setSize(int.Parse(dataReader.GetValue(9).ToString()));
            ((dynamic)field).setDeviceType(dataReader.GetString(10));
            ((dynamic)field).setAlarm("1".Equals(dataReader.GetValue(13).ToString()));
            ((dynamic)field).setAlarmName(dataReader.GetString(14));
            ((dynamic)field).setAlarmValue(dataReader.GetString(15));
            ((dynamic)field).setLoop(dataReader.GetString(11));
            ((dynamic)field).setCode(dataReader.GetString(12));
            ((dynamic)field).setIsSave(dataReader.GetValue(16).ToString());
            ((dynamic)field).setLayer(int.Parse(dataReader.GetValue(19).ToString()));

            //如果是SchemaDictionary，即使数据库没有勾选保存，也默认保存，否则后面的取字段的逻辑可能会取不到数据
            if (field is SchemaDictionary)
            {
                ((dynamic)field).setIsSave("1");
            }
            schema.Add(schema.Count, field);
        }
    }
}
